import os
import sqlite3
import sys
import datetime
import hashlib

def init_database():
    """
    初始化数据库
    - 检查数据库目录是否存在，如不存在则创建
    - 检查数据库文件是否存在，如不存在则创建并初始化表结构
    """
    db_dir = r"C:\military_engineering_db"
    db_path = os.path.join(db_dir, "military_engineering.db")
    
    # 检查并创建数据库目录
    if not os.path.exists(db_dir):
        try:
            os.makedirs(db_dir)
            print(f"已创建数据库目录: {db_dir}")
        except Exception as e:
            print(f"创建数据库目录失败: {str(e)}")
            return False
    
    # 检查数据库文件是否存在
    db_exists = os.path.exists(db_path)
    
    # 连接数据库（如果不存在则创建）
    try:
        conn = sqlite3.connect(db_path)
        cursor = conn.cursor()
        print(f"已连接到数据库: {db_path}")
        
        # 如果数据库文件不存在，初始化表结构
        if not db_exists:
            print("正在初始化数据库表结构...")
            
            # 创建项目表
            cursor.execute('''
            CREATE TABLE IF NOT EXISTS projects (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                project_number INTEGER NOT NULL,
                project_code TEXT,
                name TEXT NOT NULL,
                description TEXT,
                location TEXT,
                status TEXT NOT NULL,
                sub_status TEXT,
                progress REAL DEFAULT 0,
                phase_status TEXT,
                task_document_no TEXT,
                planning_type TEXT,
                construction_type TEXT,
                total_investment REAL,
                direct_cost REAL,
                task_issue_date TEXT,
                required_completion_date TEXT,
                extended_completion_date TEXT,
                scheme_design_confirm_date TEXT,
                preliminary_design_confirm_date TEXT,
                construction_drawing_review_date TEXT,
                bill_compilation_date TEXT,
                bill_audit_date TEXT,
                construction_drawing_confirm_date TEXT,
                bidding_announcement_date TEXT,
                enterprise_registration_date TEXT,
                bid_opening_date TEXT,
                bid_award_date TEXT,
                bid_notice_date TEXT,
                contract_signing_date TEXT,
                direct_commission_date TEXT,
                quality_supervision_registration_date TEXT,
                construction_permit_date TEXT,
                construction_start_order_date TEXT,
                preliminary_acceptance_date TEXT,
                archive_acceptance_date TEXT,
                completion_acceptance_date TEXT,
                entity_handover_date TEXT,
                archive_handover_date TEXT,
                completion_filing_date TEXT,
                settlement_completion_date TEXT,
                final_account_completion_date TEXT,
                bidding_mode TEXT,
                contract_start_date TEXT,
                contract_end_date TEXT,
                contract_duration INTEGER,
                contract_amount REAL,
                survey_design_unit TEXT,
                cost_compilation_unit TEXT,
                cost_audit_unit TEXT,
                construction_unit TEXT,
                supervision_unit TEXT,
                using_unit TEXT,
                using_unit_name TEXT,
                supply_unit TEXT,
                field_contact TEXT,
                contact_phone TEXT,
                current_week_work TEXT,
                next_week_plan TEXT,
                visual_progress TEXT,
                issues TEXT,
                solutions TEXT,
                payment_progress TEXT,
                created_at TEXT NOT NULL,
                updated_at TEXT NOT NULL,
                notes TEXT
            )
            ''')
            
            # 创建里程碑节点表
            cursor.execute('''
            CREATE TABLE IF NOT EXISTS milestones (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                project_id INTEGER NOT NULL,
                name TEXT NOT NULL,
                category TEXT NOT NULL,
                planned_date TEXT,
                actual_date TEXT,
                is_custom INTEGER DEFAULT 0,
                progress_update_id INTEGER,
                notes TEXT,
                created_at TEXT NOT NULL,
                updated_at TEXT NOT NULL,
                FOREIGN KEY (project_id) REFERENCES projects (id) ON DELETE CASCADE,
                FOREIGN KEY (progress_update_id) REFERENCES progress_updates (id) ON DELETE SET NULL
            )
            ''')
            
            # 创建进度更新表
            cursor.execute('''
            CREATE TABLE IF NOT EXISTS progress_updates (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                project_id INTEGER NOT NULL,
                update_date TEXT NOT NULL,
                week_number INTEGER,
                current_progress TEXT,
                next_plan TEXT,
                issues TEXT,
                visual_progress TEXT,
                solutions TEXT,
                payment_progress TEXT,
                created_at TEXT NOT NULL,
                updated_at TEXT NOT NULL,
                FOREIGN KEY (project_id) REFERENCES projects (id) ON DELETE CASCADE
            )
            ''')
            
            # 创建系统设置表
            cursor.execute('''
            CREATE TABLE IF NOT EXISTS settings (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                key TEXT NOT NULL UNIQUE,
                value TEXT,
                description TEXT,
                category TEXT NOT NULL,
                updated_at TEXT NOT NULL
            )
            ''')
            
            # 创建用户表
            cursor.execute('''
            CREATE TABLE IF NOT EXISTS users (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                username TEXT NOT NULL UNIQUE,
                password_hash TEXT NOT NULL,
                full_name TEXT,
                role TEXT NOT NULL,
                email TEXT,
                phone TEXT,
                last_login TEXT,
                created_at TEXT NOT NULL,
                updated_at TEXT NOT NULL
            )
            ''')
            
            # 创建日志表
            cursor.execute('''
            CREATE TABLE IF NOT EXISTS logs (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                user_id INTEGER,
                action TEXT NOT NULL,
                target_type TEXT,
                target_id INTEGER,
                details TEXT,
                created_at TEXT NOT NULL,
                FOREIGN KEY (user_id) REFERENCES users (id)
            )
            ''')
            
            # 创建文件表
            cursor.execute('''
            CREATE TABLE IF NOT EXISTS files (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                project_id INTEGER NOT NULL,
                file_name TEXT NOT NULL,
                file_path TEXT NOT NULL,
                file_type TEXT,
                file_size INTEGER,
                upload_user_id INTEGER,
                description TEXT,
                created_at TEXT NOT NULL,
                updated_at TEXT NOT NULL,
                FOREIGN KEY (project_id) REFERENCES projects (id),
                FOREIGN KEY (upload_user_id) REFERENCES users (id)
            )
            ''')
            
            # 创建任务表
            cursor.execute('''
            CREATE TABLE IF NOT EXISTS tasks (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                project_id INTEGER NOT NULL,
                title TEXT NOT NULL,
                description TEXT,
                status TEXT NOT NULL,
                priority TEXT,
                assigned_to INTEGER,
                due_date TEXT,
                completed_date TEXT,
                created_by INTEGER,
                created_at TEXT NOT NULL,
                updated_at TEXT NOT NULL,
                FOREIGN KEY (project_id) REFERENCES projects (id),
                FOREIGN KEY (assigned_to) REFERENCES users (id),
                FOREIGN KEY (created_by) REFERENCES users (id)
            )
            ''')
            
            # 创建评论表
            cursor.execute('''
            CREATE TABLE IF NOT EXISTS comments (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                project_id INTEGER NOT NULL,
                user_id INTEGER NOT NULL,
                content TEXT NOT NULL,
                created_at TEXT NOT NULL,
                updated_at TEXT NOT NULL,
                FOREIGN KEY (project_id) REFERENCES projects (id),
                FOREIGN KEY (user_id) REFERENCES users (id)
            )
            ''')
            
            # 创建索引
            print("正在创建索引...")
            
            # 项目表索引
            cursor.execute('CREATE INDEX IF NOT EXISTS idx_projects_status ON projects (status)')
            cursor.execute('CREATE INDEX IF NOT EXISTS idx_projects_progress ON projects (progress)')
            cursor.execute('CREATE INDEX IF NOT EXISTS idx_projects_project_code ON projects (project_code)')
            cursor.execute('CREATE INDEX IF NOT EXISTS idx_projects_project_number ON projects (project_number)')
            
            # 里程碑节点表索引
            cursor.execute('CREATE INDEX IF NOT EXISTS idx_milestones_project_id ON milestones (project_id)')
            cursor.execute('CREATE INDEX IF NOT EXISTS idx_milestones_category ON milestones (category)')
            cursor.execute('CREATE INDEX IF NOT EXISTS idx_milestones_is_custom ON milestones (is_custom)')
            cursor.execute('CREATE INDEX IF NOT EXISTS idx_milestones_progress_update_id ON milestones (progress_update_id)')
            
            # 进度更新表索引
            cursor.execute('CREATE INDEX IF NOT EXISTS idx_progress_updates_project_id ON progress_updates (project_id)')
            cursor.execute('CREATE INDEX IF NOT EXISTS idx_progress_updates_update_date ON progress_updates (update_date)')
            
            # 初始化系统设置
            print("正在初始化系统设置...")
            now = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
            
            # 预定义项目状态
            cursor.execute('''
            INSERT INTO settings (key, value, description, category, updated_at)
            VALUES (?, ?, ?, ?, ?)
            ''', ('default_project_status', '前期-方案设计,前期-施工图设计及清单编制,前期-清单审核,发包-发包方案编制,发包-发包组织实施,发包-评标结果公示,施工-待开工,施工-在建,施工-初步验收,竣工-竣工验收,竣工-实体移交,竣工-完成结算,竣工-完成决算,撤项', '默认项目阶段选项', 'project', now))
            
            # 预定义阶段状态
            cursor.execute('''
            INSERT INTO settings (key, value, description, category, updated_at)
            VALUES (?, ?, ?, ?, ?)
            ''', ('default_phase_status', '正常进行,阶段性迟缓,滞后', '默认阶段状态选项', 'project', now))
            
            # 预定义里程碑分类
            cursor.execute('''
            INSERT INTO settings (key, value, description, category, updated_at)
            VALUES (?, ?, ?, ?, ?)
            ''', ('default_milestone_categories', '前期,发包,施工,竣工', '默认里程碑分类', 'milestone', now))
            
            # 预定义发包模式
            cursor.execute('''
            INSERT INTO settings (key, value, description, category, updated_at)
            VALUES (?, ?, ?, ?, ?)
            ''', ('default_bidding_modes', '招标,直接委托', '默认发包模式', 'contract', now))
            
            # 预定义里程碑节点
            cursor.execute('''
            INSERT INTO settings (key, value, description, category, updated_at)
            VALUES (?, ?, ?, ?, ?)
            ''', ('default_milestones_early', '任务书下达,方案设计确认时间,初步设计确认时间,完成施工图审查时间,完成清单编制时间,完成清单审核时间,施工图确认时间', '前期阶段默认节点', 'milestone', now))
            
            cursor.execute('''
            INSERT INTO settings (key, value, description, category, updated_at)
            VALUES (?, ?, ?, ?, ?)
            ''', ('default_milestones_bidding', '企业邀请/招标公告发布,企业报名时间,开标时间,定标时间,中标通知书发放时间,合同签订时间,直接委托时间', '发包阶段默认节点', 'milestone', now))
            
            cursor.execute('''
            INSERT INTO settings (key, value, description, category, updated_at)
            VALUES (?, ?, ?, ?, ?)
            ''', ('default_milestones_construction', '质监注册时间,施工许可证时间,开工令时间,初步验收时间,档案验收时间,竣工验收时间', '施工阶段默认节点', 'milestone', now))
            
            cursor.execute('''
            INSERT INTO settings (key, value, description, category, updated_at)
            VALUES (?, ?, ?, ?, ?)
            ''', ('default_milestones_completion', '实体移交时间,档案移交时间,竣工备案时间,完成结算时间,完成决算时间', '竣工阶段默认节点', 'milestone', now))
            
            # 基本设置
            cursor.execute('''
            INSERT INTO settings (key, value, description, category, updated_at)
            VALUES (?, ?, ?, ?, ?)
            ''', ('theme_color', '#1890ff', '系统主题色', 'ui', now))
            
            cursor.execute('''
            INSERT INTO settings (key, value, description, category, updated_at)
            VALUES (?, ?, ?, ?, ?)
            ''', ('font_size', 'medium', '系统字体大小（small/medium/large）', 'ui', now))
            
            # 建设方式选项
            cursor.execute('''
            INSERT INTO settings (key, value, description, category, updated_at)
            VALUES (?, ?, ?, ?, ?)
            ''', ('default_construction_types', '统建,代建', '默认建设方式选项', 'project', now))
            
            # 使用单位选项
            cursor.execute('''
            INSERT INTO settings (key, value, description, category, updated_at)
            VALUES (?, ?, ?, ?, ?)
            ''', ('default_using_unit_options', '中心及下属单位,代供单位', '默认使用单位选项（自定义输入不在此列表中）', 'project', now))
            
            # 创建默认管理员用户
            # 默认管理员用户名和密码
            admin_username = "admin"
            admin_password = "admin123"  # 实际应用中应使用更强的密码
            
            # 简单的密码哈希（实际应用中应使用更安全的方法如bcrypt）
            password_hash = hashlib.sha256(admin_password.encode()).hexdigest()
            
            # 插入管理员用户
            cursor.execute('''
            INSERT INTO users (username, password_hash, full_name, role, created_at, updated_at)
            VALUES (?, ?, ?, ?, ?, ?)
            ''', (admin_username, password_hash, "系统管理员", "admin", now, now))
            
            # 提交更改
            conn.commit()
            print("数据库初始化完成！")
            print(f"已创建默认管理员用户: {admin_username}，密码: {admin_password}")
        
        # 关闭连接
        cursor.close()
        conn.close()
        return True
        
    except Exception as e:
        print(f"数据库初始化失败: {str(e)}")
        return False

if __name__ == "__main__":
    success = init_database()
    if success:
        print("数据库初始化成功！")
    else:
        print("数据库初始化失败！")
        sys.exit(1) 